package com.tyl.dao.user;

import com.mysql.jdbc.StringUtils;
import com.tyl.dao.BaseDao;
import com.tyl.pojo.User;

import javax.sql.rowset.spi.SyncResolver;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao{

    public User getLoginUser(Connection connection, String userCode,String userPassword) throws SQLException {

        PreparedStatement psmt=null;
        ResultSet rs=null;
        User user=null;
        if (connection!=null)
        {
            String sql="select *from smbms_user where userCode=?and userPassword=?";
            //给查询字段赋值
            Object[] params={userCode,userPassword};
            rs= BaseDao.execute(connection,psmt,sql,rs,params);
            if (rs.next()){
                user=new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setCreationDate(rs.getDate("creationDate"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setModifyDate(rs.getTimestamp("modifyDate"));
            }
            BaseDao.closeResource(null,psmt,rs);
        }
        return user;
    }

    //修改密码实现
    public int updatePwd(Connection connection, int id, String password) throws SQLException {

        PreparedStatement pstm=null;
        int execute=0;
        if (connection!=null){
            String sql="update smbms.smbms_user set userPassword =? where id=?";
            Object[] params={password,id};
            execute = BaseDao.execute(connection,pstm, sql, params);
            BaseDao.closeResource(connection,null,null);
        }
        return execute;
    }

    //根据用户名或者用户角色查询用户总数
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
        PreparedStatement pstm=null;
        ResultSet rs=null;
        int count=0;
        if (connection!=null){
            StringBuilder sql=new StringBuilder();
            sql.append("select count(1) as count from smbms_user u ,smbms_role r where u.userRole=r.id");
            ArrayList<Object> list = new ArrayList<Object>();

            if (!StringUtils.isNullOrEmpty(username)){
                sql.append(" and u.userName like ?");
                list.add("%"+username+"%");//index0
            }
            if(userRole>0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);//index1
            }
            Object[] params = list.toArray();
            System.out.println(sql.toString());//输出最完整的sql语句
            rs=BaseDao.execute(connection,pstm,sql.toString(),rs,params);
            if(rs.next()){
                 count = rs.getInt("count");//从结果集中获取最终数量
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return count;
    }
    //获取用户列表
    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) {
        PreparedStatement pstm=null;
        ResultSet rs=null;
        ArrayList<User> userList = new ArrayList<User>();
        if (connection!=null){
            ArrayList<Object> list = new ArrayList<Object>();
            StringBuilder sql=new StringBuilder();
            sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
            //拼接sql
            if(!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and userName like ? ");
                list.add("%"+userName+"%");//index0
            }
            if (userRole>0){
                sql.append(" and u.userRole=?");
                list.add(userRole);//index1
            }
            //分页
            sql.append(" order by creationDate DESC limit ?,?");
            currentPageNo=(currentPageNo-1)*pageSize;
            list.add(currentPageNo);//index2
            list.add(pageSize);//index3
            Object[] params = list.toArray();
            System.out.println(sql.toString());
            try {
                 rs = BaseDao.execute(connection, pstm, sql.toString(), rs, params);
                 while (rs.next()){
                     User user=new User();
                     user.setId(rs.getInt("id"));
                     user.setUserCode(rs.getString("userCode"));
                     user.setUserName(rs.getString("userName"));
                     user.setGender(rs.getInt("gender"));
                     user.setPhone(rs.getString("phone"));
                     user.setBirthday(rs.getDate("birthday"));
                     user.setUserRole(rs.getInt("userRole"));
                     user.setUserRoleName(rs.getString("userRoleName"));
                     userList.add(user);
                 }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                BaseDao.closeResource(connection,pstm,rs);
            }

        }
        return userList;
    }

    //添加用户
    public int addUser(Connection connection, User user) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        if (null != connection) {
            String sql = "insert into smbms_user (userCode,userName,userPassword," +
                    "userRole,gender,birthday,phone,address,creationDate,createdBy) " +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            System.out.println(sql);
            Object[] params = {user.getUserCode(), user.getUserName(), user.getUserPassword(),
                    user.getUserRole(), user.getGender(), user.getBirthday(),
                    user.getPhone(), user.getAddress(), user.getCreationDate(), user.getCreatedBy()};
            updateRows = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null, pstm, null);
        }
        return updateRows;
    }

    //通过用户编码查询用户
    public User getUserCode(Connection connection, String userCode) throws SQLException {
        PreparedStatement pstm=null;
        ResultSet rs=null;
        User user=null;
        if(connection!=null){

            String sql="SELECT * FROM smbms_user where userCode=?";
            Object[]parms={userCode};
            rs = BaseDao.execute(connection, pstm, sql, rs, parms);
            if (rs.next()){
                user=new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setCreationDate(rs.getDate("creationDate"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setModifyDate(rs.getTimestamp("modifyDate"));
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return user;
    }

    //根据用户ID查询用户
    public User getUserId(Connection connection, String userId) throws SQLException {
        PreparedStatement pstm=null;
        ResultSet rs=null;
        User user=null;
        if(connection!=null){
            String sql="SELECT * FROM smbms_user where id=?";
            Object[]parms={userId};
            rs = BaseDao.execute(connection, pstm, sql, rs, parms);
            if (rs.next()){
                user=new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                user.setUserRole(rs.getInt("userRole"));
                user.setCreatedBy(rs.getInt("createdBy"));
                user.setCreationDate(rs.getDate("creationDate"));
                user.setModifyBy(rs.getInt("modifyBy"));
                user.setModifyDate(rs.getTimestamp("modifyDate"));
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return user;
    }
    //删除用户
    public int deleteByUserId(Connection connection, Integer userId) throws SQLException {
        PreparedStatement pstm=null;
        Object[]pamas={userId};
        int flag=0;
        if (connection!=null){
            String sql="delete from smbms_user where id=?";
            flag = BaseDao.execute(connection, pstm, sql, pamas);
            BaseDao.closeResource(null,pstm,null);
        }
        return flag;
    }
    // 修改用户信息
    public int updateUser(Connection connection, User user) {
        PreparedStatement pstm=null;
        int flag=0;
        if (connection!=null){
            String sql="update smbms_user set userName=?,gender=?,birthday=?,phone=?," +
                    "address=?,userRole=?,modifyBy=?,modifyDate=? where id = ?";
            Object[] pamas={user.getUserName(), user.getGender(), user.getBirthday(),
                    user.getPhone(), user.getAddress(), user.getUserRole(), user.getModifyBy(),
                    user.getModifyDate(), user.getId()};
            try {
                flag = BaseDao.execute(connection, pstm, sql, pamas);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                BaseDao.closeResource(null,pstm,null);
            }
        }
        return flag;
    }
}
